package org.xu.logic;

import cn.hutool.poi.excel.ExcelReader;
import com.core.exception.BzException;
import com.core.utils.UUIDGenerator;
import lombok.extern.slf4j.Slf4j;
import org.apache.poi.ss.usermodel.WorkbookFactory;
import org.springframework.stereotype.Service;
import org.springframework.util.CollectionUtils;
import org.springframework.web.multipart.MultipartFile;
import org.xu.bo.ExcelBO;

import java.util.List;

@Slf4j
@Service
public class SQLGenerateInsertLogicImpl implements SQLGenerateInsertLogic{

    @Override
    public void generateInsertSQL(MultipartFile file) {
        List<ExcelBO> list = generate(file);
        for (ExcelBO excelBO : list) {
            String tableName = "sg_vehicle_trailer_annual_inspect_info";
            String sql = "INSERT into table (id, vin, apply_no, yearly_status_code, yearly_status_name, annual_inspect_remark, predict_annual_inspect_date, predict_scrap_date, current, deleted,version,creator_id, create_time) VALUES(" ;
            // insert into `sg_vehicle_annual_inspect_info` (`id`, `vin`, `apply_no`, `yearly_status_code`, `yearly_status_name`, `annual_inspect_remark`, `predict_annual_inspect_date`, `predict_scrap_date`, `current`, `version`, `deleted`, `creator_id`, `create_time`, `updater_id`, `update_time`)
            // values('018d2f3bc9384028e6c18d2f3bc90000','LFV3A24F973009821','1000175882','not_annual_inspect','不年检',NULL,'2019-11-30','2022-11-30','',NULL,'',NULL,'2024-01-22 11:34:50',NULL,'2024-01-22 11:34:50');
            sql = sql.replaceAll("table", tableName);
            String values = getValues(excelBO);
            sql = sql + values + ");";
            System.out.println(sql);
        }
    }

    private String getValues(ExcelBO bo){
        String now = "Now()";
        return getValue2(UUIDGenerator.generate()) +"," + getValue2(bo.getTrailer_vin()) +","  + getValue2(bo.getApply_no()) +"," + getValue2(bo.getYearly_status_code()) +"," + getValue2(bo.getYearly_status_name()) +"," + getValue2(bo.getAnnual_inspect_remark())
                +"," + getValue2(bo.getPredict_annual_inspect_date()) +"," + getValue2(bo.getPredict_scrap_date()) +"," + getValue2(1) +"," + false +  "," + 1 + "," + getValue2(0) +"," + now;
    }

    private List<ExcelBO> generate(MultipartFile file){
        try {
            // 存储 处理的excel
            List<ExcelBO> excelList;
            ExcelReader reader = new ExcelReader(WorkbookFactory.create(file.getInputStream()), 0);
            reader.setHeaderAlias(ExcelBO.HEADER_ALIAS);
            excelList = reader.read(0, 0, ExcelBO.class);
            if (CollectionUtils.isEmpty(excelList)) {
                throw new BzException("excel数据为空,导入失败");
            }
            return excelList;
        }catch (BzException e){
            throw e;
        }catch (Exception e) {
            log.info("解析 excel异常，", e);
            throw new BzException("解析 excel异常");
        }
    }

    private String getValue2(Object obj){
        if (obj == null){
            return "''";
        }
        if (obj instanceof String){
            if (((String) obj).trim().equalsIgnoreCase("-")){
                return null;
            }
        }
        return "'" + obj.toString() + "'";
    }

}
